# encoding:utf-8
"""
门店相关sql
"""

SearchStoreSql = "SELECT * FROM MCCP_ST_Store"

# 首页数据存储过程
# 已结金额，应收总额，未结金额，用餐人数，桌均消费，人均消费，昨日金额，本月总额
IndexDataStoreHandleSQL = """
DECLARE	@return_value int
    EXEC	@return_value = [dbo].[SP_Report_HomePage_StStore_Phone]
            @BrandID =  N'{BrandID}',
            @StoreID = N'{StoreID}'
    SELECT	'Return Value' = @return_value
"""

# 菜品销售数据存储过程
ProductOrderDetailsStoreHandleSQL = """
DECLARE	@return_value int
    EXEC	@return_value = [dbo].[SP_Report_StoreHandle_ProductOrderDetails_Function]
            @DateB = N'{DateB}',
            @DateE = N'{DateE}',
            @KeyWord = N'{KeyWord}',
            @makeSmallID = N'{makeSmallID}',
            @StoreID = N'{StoreID}',
            @BrandID = N'{BrandID}'
    SELECT	'Return Value' = @return_value
"""


# 异常操作查询
AbnormalOperationSQL = """
    SELECT * FROM T_ReportStoreHandleAbnormalOperationDetails WHERE StoreID='{StoreID}'
"""

# 调用时段菜品销售情况存储过程
TimeProductStatisticsSQL = """
DECLARE	@return_value int
    EXEC @return_value = [dbo].[SP_PhoneReport_TimeProductStatistics]
		@Option = N'BetweenDate',
		@DateB = N'{DateB}',
		@DateE = N'{DateE}',
		@StoreID = N'{StoreID}',
		@TimeNum = {TimeNum},
		@PageIndex = 1,
		@PageSize = 10
	SELECT	'Return Value' = @return_value
"""

# 支付方式占比
PaymentProportionSearchSQL = """
DECLARE	@return_value int
    EXEC @return_value = [dbo].[SP_PaymentProportion_Get]
		@StoreID = N'{StoreID}',
		@BrandID = N'{BrandID}'
	SELECT	'Return Value' = @return_value
"""

# 菜品赠送查询
GiveProductSaleSQL = """
SELECT     T_BILL.STOREID, T_BILL.BRANDID,  T_BILL.CHECKOUTTIME, T_BILL.CREATETIME AS BILL_CREATETIME, T_BILL_ITEM.CONTENT,T_BILL_ITEM.PRICE,T_BILL_ITEM.STOCK,T_BILL_ITEM.CREATETIME
FROM         T_BILL INNER JOIN
                      T_BILL_ITEM ON T_BILL.SERIALNUM = T_BILL_ITEM.SERIALNUM where T_BILL_ITEM.EXPLAIN ='赠送' 
                      and T_BILL.STOREID='{StoreID}' and 
                      T_BILL.CREATETIME>='{DayStartTime}' and T_BILL.CREATETIME<='{DayEndTime}'
"""
# 今日与上周同日对比数据

SaleCompareWithLastWeekSQL = """
SELECT * FROM T_ReportHomePage_StStore WHERE [门店ID]= '{StoreID}' and [创建日期]>='{DayStartTime}' and [创建日期]<='{DayEndTime}'


"""

# 执行营业日报存储查询
StoreFinancialOperationAnalysisSQL = """
DECLARE	@return_value int
    EXEC @return_value = [dbo].[SP_Report_StoreFinancial_OperationAnalysis_Function]
		@DateB = N'{DateB}',
		@DateE = N'{DateE}',
		@StoreID = N'{StoreID}'
	SELECT	'Return Value' = @return_value
"""

# 退品查询
SearchRetreatProductSQL = """
        SELECT * FROM T_ReportStoreHandleProductRetreatDetails WHERE StoreID='{StoreID}'
"""
